此文是《10周入門數據分析》系列的第5篇

想了解學習路線，可以先閱讀「10周計劃」
前一篇分享了《Excel資料分析必掌握的43個公式》，今天這篇講實操，教大家用Excel做一次簡單的分析。一是讓大家瞭解資料分析是一個怎樣的流程；其次熟練Excel的操作(學的知識要利用起來)，包括公式，樞紐分析表等。

這裡我用Python在智聯招聘上爬取了約1800條的BI工程師的職位資訊，並且將崗位名稱、公司名稱、薪水、所在城市、所屬行業、學歷要求、工作年限這些關鍵資訊用CSV檔保存下來。

操作版本：Excel 2016 ，WIN 10

一個完整的資料分析都需要經歷這樣幾個步驟：

§ 資料獲取 — — 這裡我已經用Python爬好了；

§ 明確分析目的 — — 你拿這資料要得到什麼資訊，解決什麼問題；

§ 觀察資料 — — 各個資料欄位的含義，中英文釋義；

§ 資料清洗 — — 無效值、缺失值、重複值處理，資料結構是否一致等；

§ 分析過程 — — 圍繞目的展開分析；

§ 製作視覺化 — — 做圖表做視覺化報告。

一、明確目的

資料分析的大忌是不知道分析的方向和目的，拿著一堆資料不知所措。資料用來解決什麼問題？
是進行匯總統計製作成報表？

是進行資料視覺化，作為一張資訊圖？

是驗證某一類業務假設？

是希望提高某一個指標的KPI？

要知道一切資料分析都是以業務為核心目的，所以要找到業務問題的思考點。關於找到問題的切入點，之前資料分析思維篇講過。永遠不要妄圖在一堆資料中找結論，目標在前，資料在後，哪怕是把資料做個平均值比較，也比沒有方向好。每一步嘗試都會引發進一步思考，比如為什麼這個值這麼低，原因在哪裡，這個差異波動有何規律……

所以，分析前不妨先來看一下我們爬的資料：

From 數據分析那些事
假設我是一個BI工程師，我想知道：

目前BI工程師的平均薪資水準如何，薪資的區間分佈如何

各地區對BI工程師的需求量是多少，哪些地區設崗最多。

不同年限的BI工程師薪資差異如何，3年後我差不多是什麼樣的價位？

薪水較高的公司有哪些？

帶著這樣的問題，那我們的分析就有了方向，後續則是將目標拆解為實際分析展示的過程。

二、瞭解資料概況

拿到資料肯定是要先看一下的，你想要的資料全不全，拿到的資料有哪些可分析之處。主要就是看資料欄位，要瞭解資料欄位的含義：

JobName — — 崗位名稱

Company — — 公司名

Salary — — 薪水

City — — 城市

Jobtype — — 崗位領域

Edulevel — — 學歷要求

WorkingExp — — 工作年限要求

三、數據清洗

接下來進行資料清洗。資料清洗一般包括無效值、缺失值、重複值處理；資料是否有亂碼，錯位現象；資料口徑問題，兩張表的關聯ID名是否一致；還有是否有統一的標準或命名，如公司名全寫或縮寫的區分。資料轉換則是將資料規整為統一格式處理。因為這是只是Excel級別的資料分析，且就一張簡單的資料表，不會有太多複雜的操作。這裡簡單總結下。

1、有無缺失值

資料的缺失會很大程度影響分析結果。資料缺失的原因很多，比如資料獲取的時候，因為技術的原因，爬蟲沒有完全抓去。但工作上更多的原因是資料入庫的時候就沒有收集全，有沒填有遺漏，這又是資料規範資料治理的話題了。一般來說，如果某一欄位資料缺失超過40%~50%，就沒有分析意義了，考慮刪除或作其他措施。

看資料有沒有缺失，只要在Excel中選中該列看計數。

這裡，eduLevel有缺失（1759/1800）但不多，不影響實際分析。

2、髒資料處理

發現jobName列裡面有一些類似BIM工程師的崗位資訊，這些應該都是土木行業的工程師，爬去時沒做過濾，還有包含“bim”“BIOS””BIW”等欄位。

因為包含多重過濾，這裡我建立輔助列，設立判斷條件，然後進行篩選過濾。

=IF(OR(COUNTIF(A5,””&{“bim”,”BIM”,”BIOS”,”BIW”}&””)),1,”0")

公式的意思是，如果含有這些欄位中的任何一個則為1，否則為0。這裡我們需要篩選出結果為0的資料，總計篩選下來600多條，資料還是很髒的。

多重篩選，還可以用資料選項卡裡的高級篩選功能，就不掩飾了。

3、重復資料

重復資料一般對唯一標識欄位來處理，比如使用者ID，訂單ID，公司ID這些，這些欄位都代表這一行資料是唯一存在的。嚴格來講，這裡的表應該存在公司ID這一欄位，爬取資料的問題，我這就懶得再重爬了，就對Company欄位做重複值處理。

這裡有一個快速竅門，使用Excel的刪除重複項功能，快速定位是否有重復資料。對company列進行重複項刪除操作

只剩下562個值了。到此，一些髒資料基本清理的差不多了。

最後，salary有一些資料是“薪資面議”，“校招”的，這裡也一併過濾掉。Jobtype過濾掉汽車、電子等行業，只留包含IT互聯網行業，最後剩下不到500條資料。

4、資料再加工

一者是salary薪水用了幾K表示，這是文本，不能直接用於計算。而且還是一個範圍，後續得按照最高薪水和最低薪水拆成兩列。

二者由於城市欄位存儲有的資料為“城市-區域”格式，例如“上海-徐匯區”，為了方便分析每個城市的資料，最後新增列“城市”，截取“-”前面的真實城市資料。

為了方便整理，和原資料區分，也防止原資料丟失，這裡把之前處理的資料複製粘貼到另一張表裡。
① 薪水處理
將salary拆成最高薪水和最低薪水有三種辦法。
一是直接分列，以”-”為拆分符，得到兩列資料，然後利用替換功能刪除 k這個字串。得到結果。
二是自動填充功能，填寫已填寫的內容自動計算填充所有列。
三是利用文本查找，重點講一下這個。
寫公式的思路是，先查找第一個K出現的位置，然後再-1，去除掉K。所以公式是：

=LEFT(C2,FIND(“K”,C2,1)-1)

同樣的思路，最高薪水需要利用find查找”-”位置,然後截取 從”-” 到最後第二個位置的字串。

=MID(C2,FIND(“-”,C2,1)+1,LEN(C2)-FIND(“-”,C2,1)-1)

這裡，在新增資料列，平均薪水，來近似代表實際的準確薪資。平均薪水=(薪水下限+薪水上限)/2，即可得到每個崗位的平均薪水。

②真實城市截取
由於城市欄位存儲有的資料為“城市-區域”格式，例如“上海-徐匯區”，為了方便分析每個城市的資料，最後新增列“城市”，截取“-”前面的真實城市資料。

=IF(COUNTIF(G2,”-”)=0,G2,LEFT(G2,FIND(“-”,G2,1)-1))

至此，所有資料清洗加工完畢，食材已經全部準備好，下面可以正式開始資料視覺化的美食下鍋烹飪了。

四、分析過程

分析過程有很多玩法，因為這裡主要資料均是文本格式，資料又很簡單，所以偏向匯總統計的計算。如果數值型的資料比較多，就會涉及到統計、比例等概念。如果有時間類資料，那麼還會有趨勢、變化的概念。

整體分析使用樞紐分析表完成，先利用樞紐分析表獲得匯總型統計。

1、BI工程師需求概況分析

這裡我簡單加了一下增材區分，增加資料大小的辨識度。（條件格式 — — 色階）
看來北上廣深的BI工程師崗位遠多於其他城市，成都杭州武漢梯隊次之。1~3年以及3~5年經驗的缺口相當。

2、BI工程薪資情況分析

各經驗年齡的平均薪資狀況，差距梯度還是很明顯的。

目前市面上BI工程的薪資主要分許在7~17K左右區間。23~26K，應該是5~10年左右經驗的崗位也相當。

3、薪資變化隨著經驗的增長，學歷影響力的大小

整體來說，BI工程師大專和本科的薪資差異並不是很大，3~5年經驗，本科稍佔優勢。到5~10年，基本拉平，也就是說學歷因素影響比重更弱，這時候更看重經驗。

其他的分析過程就不多做贅述了，主要是使用樞紐分析表和資料透視圖進行多維度（城市，學歷，工作經驗）的分析，沒有其他複雜的技巧。

關於資料透視圖和樞紐分析表。選中所要分析的資料列，2013版以上的Excel基本上都很智慧的幫你推薦圖示，生成透視介面，只要分清楚拖拽的欄位事到列，到值還是到行即可。然後視情況多資料做一定篩選，因為資料清洗得不一定很徹底，我在製作的過程中就忽略了一些欄位的空缺值，又回過頭做了過濾。

最後

到此，一個簡單的資料分析基本結束了。因為資料簡單，並沒有涉及過多的資料整合，表合併，專業資料統計回歸等操作。

整個資料分析過程最費時間的資料清理，大約佔據70%，只要明確了目的，視覺化分析師很簡單的。

其次，也可以看到，用Excel做分析，更多的優勢是資料的簡單處理。隨便過濾、查詢、定位救你呢瞭解資料的概況。但在視覺化方面比較雞肋，行列值選擇，以及複雜的圖表製作都有一些難度，一句話總結Excel視覺化要想做的好看還是要費點時間的。

所以我在分析的時候，基本上就是用Excel看看資料全貌，簡單處理下。分析、視覺化什麼的還是會交給BI。後面，我會再出一篇用BI製作的教程。

關於學習計畫

本文是《10周入門資料分析》系列的第5篇，小編因爲在大陸工作了5年，所以爬取了大陸比較火的招聘網站，而且這篇文章，是小編之前做的，也在大陸發佈過，表格有些不清晰，請見諒。

想瞭解更多的資料分析知識，請關注我的Facebook, 期待你與我互動起來啦~